package ru.agiledev.akb.server.report;

import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.hibernate.Criteria;
import org.hibernate.Session;
import org.hibernate.criterion.Restrictions;
import ru.agiledev.akb.server.domain.Aspect;
import ru.agiledev.akb.server.domain.Example;
import ru.agiledev.akb.server.domain.IObject;
import ru.agiledev.akb.server.domain.presentation.Addition;
import ru.agiledev.akb.server.domain.presentation.Author;
import ru.agiledev.akb.server.domain.presentation.Page;
import ru.agiledev.akb.server.hibernate.HibernateUtil;
import ru.agiledev.akb.server.util.Translate;

import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * Created by a.kirillov
 * Date: 5/12/11 Time: 11:26 AM
 */
public class ReportManager {
    public static String createReport(String path, Integer authorId){

        HSSFWorkbook wb = new HSSFWorkbook();

        Session session = HibernateUtil.getSessionFactory().openSession();

        Criteria criteria = session.createCriteria(Author.class);
        List<Author> aList = criteria.add(Restrictions.eq("id", authorId)).list();
        Author author = aList.get(0);

        Map<String, HSSFCellStyle> styles = createStyles(wb);

        HSSFSheet sheet = wb.createSheet("Отчет для пользователя "+author.getFirstName() +" "+author.getLastName());

        sheet.setColumnWidth(0, 15*256);
        sheet.setColumnWidth(1, 21*256);
        sheet.setColumnWidth(2, 50*256);

        Row titleRow = sheet.createRow(0);
        titleRow.setHeightInPoints(15);
        Cell cell = titleRow.createCell(0);
        cell.setCellValue("Автор");
        cell.setCellStyle(styles.get("title"));

        sheet.createRow(1);

        cell = titleRow.createCell(1);
        String affilation = author.getAffilation();
        String insertion = "";
        if(affilation.length()>0){
            insertion = " ("+affilation+")";
        }
        cell.setCellValue(author.getFirstName() + " " + author.getLastName()+insertion);
        cell.setCellStyle(styles.get("name"));

        CellRangeAddress cellRangeAddress = new CellRangeAddress(0,1,1,2);
        sheet.addMergedRegion(cellRangeAddress);

        Row dataRow = sheet.createRow(2);
        dataRow.setHeightInPoints(dataRow.getHeightInPoints()*2);

        cell = dataRow.createCell(0);
        cell.setCellValue("Созданные записи");
        cell.setCellStyle(styles.get("subtitle"));

        cellRangeAddress = new CellRangeAddress(2,2,0,2);
        sheet.addMergedRegion(cellRangeAddress);

        List<Page> pages = author.getHistory().getCreatedPages();

        int delta=3;
        for(Page page:pages){
            Row pageRow = sheet.createRow(delta); delta++;
            cell = pageRow.createCell(0);
            cell.setCellValue("Запись "+page.getId());
            cell.setCellStyle(styles.get("dataNames"));


            cell = pageRow.createCell(1);
            cell.setCellValue("Вопрос:");
            cell.setCellStyle(styles.get("dataNames"));
            cell = pageRow.createCell(2);
            cell.setCellValue(page.getQuestion().getValue());
            cell.setCellStyle(styles.get("data"));

            if(page.getQuestion().getValue().length()>45){
                pageRow.setHeightInPoints(pageRow.getHeightInPoints()*(page.getQuestion().getValue().length()/45 + 1));
            }

            //Type
            Row type = sheet.createRow(delta); delta++;
            cell = type.createCell(1);
            cell.setCellValue("Тип вопроса:");
            cell.setCellStyle(styles.get("dataNames"));

            cell = type.createCell(2);
            cell.setCellValue(page.getQuestion().getType().getString());

            //Objects
            Row objects = sheet.createRow(delta); delta++;
            cell = objects.createCell(1);
            cell.setCellValue("Объекты интереса:");
            cell.setCellStyle(styles.get("dataNames"));

            cell = objects.createCell(2);

            //getting objects and aspects
            List<IObject> objs = page.getQuestion().getiObjects();

            StringBuilder builder = new StringBuilder();

            for(IObject obj: objs){

                if(builder.length() > 0){
                    builder.append(", ");
                }
                builder.append(obj.getValue());
            }

            cell.setCellValue(builder.toString());

            cell.setCellStyle(styles.get("data"));
            if(builder.length()>45){
               objects.setHeightInPoints(objects.getHeightInPoints()*(builder.length()/45 + 1));
            }



            //Aspects
            Row aspects = sheet.createRow(delta); delta++;
            cell = aspects.createCell(1);
            cell.setCellValue("Аспекты:");
            cell.setCellStyle(styles.get("dataNames"));

            cell = aspects.createCell(2);
            builder = new StringBuilder();
            for(Aspect asp: page.getAspects()){

                    if(!builder.toString().contains(asp.getValue())) {
                        if(builder.length()>0){
                            builder.append(", ");
                        }
                        builder.append(asp.getValue());
                    }
                }
            cell.setCellValue(builder.toString());

            cell.setCellStyle(styles.get("data"));
            if(builder.length()>45){
               objects.setHeightInPoints(objects.getHeightInPoints()*(builder.length()/45 + 1));
            }

            //Examples
            Row examples = sheet.createRow(delta); delta++;
            cell = examples.createCell(1);
            cell.setCellValue("Примеры:");
            cell.setCellStyle(styles.get("dataNames"));

            cell = examples.createCell(2);
            List<Example> exmpls = page.getExamples();

            builder = new StringBuilder();

            for(Example exmpl: exmpls){

                if(builder.length() > 0){
                    builder.append(", ");
                }
                builder.append(exmpl.getValue());
            }

            cell.setCellValue(builder.toString());

            cell.setCellStyle(styles.get("data"));
            if(builder.length()>45){
                examples.setHeightInPoints(examples.getHeightInPoints()*(builder.length()/45 + 1));
            }

            sheet.createRow(delta); delta++;
        }


        dataRow = sheet.createRow(delta); delta++;
        dataRow.setHeightInPoints(dataRow.getHeightInPoints()*2);

        cell = dataRow.createCell(0);
        cell.setCellValue("Отредактированные записи");
        cell.setCellStyle(styles.get("subtitle"));

        cellRangeAddress = new CellRangeAddress(delta-1,delta-1,0,2);
        sheet.addMergedRegion(cellRangeAddress);

        List<Addition> additions = author.getHistory().getAdditions();

        if(additions.size()>0){
            for(Addition addition:additions){
                Row pageRow = sheet.createRow(delta); delta++;
                cell = pageRow.createCell(0);
                cell.setCellValue("Запись "+addition.getPage().getId());
                cell.setCellStyle(styles.get("dataNames"));


                cell = pageRow.createCell(1);
                cell.setCellValue("Вопрос:");
                cell.setCellStyle(styles.get("dataNames"));
                cell = pageRow.createCell(2);
                cell.setCellValue(addition.getPage().getQuestion().getValue());
                cell.setCellStyle(styles.get("data"));

                if(addition.getPage().getQuestion().getValue().length()>45){
                    pageRow.setHeightInPoints(pageRow.getHeightInPoints()*(addition.getPage().getQuestion().getValue().length()/45 + 1));
                }

                //Type
                Row type = sheet.createRow(delta); delta++;
                cell = type.createCell(1);
                cell.setCellValue("Тип вопроса:");
                cell.setCellStyle(styles.get("dataNames"));

                cell = type.createCell(2);
                cell.setCellValue(addition.getPage().getQuestion().getType().getString());

                //Objects
                Row objects = sheet.createRow(delta); delta++;
                cell = objects.createCell(1);
                cell.setCellValue("Объекты интереса:");
                cell.setCellStyle(styles.get("dataNames"));

                cell = objects.createCell(2);

                StringBuilder builder = new StringBuilder(); //string builder for use in rest of this method

                //getting objects and aspects
                List<IObject> objs = addition.getPage().getQuestion().getiObjects();

                for(IObject obj: objs){
                    if(builder.length()>0){
                        builder.append(", ");
                    }
                    builder.append(obj.getValue());
                }

                cell.setCellValue(builder.toString());
                cell.setCellStyle(styles.get("data"));
                if(builder.length()>45){
                    objects.setHeightInPoints(objects.getHeightInPoints()*(builder.length()/45 + 1));
                }

                //Aspects
                Row aspects = sheet.createRow(delta); delta++;
                cell = aspects.createCell(1);
                cell.setCellValue("Аспекты:");
                cell.setCellStyle(styles.get("dataNames"));

                cell = aspects.createCell(2);

                builder = new StringBuilder();
                for(Aspect asp: addition.getPage().getAspects()){

                    if(!builder.toString().contains(asp.getValue())) {
                        if(builder.length()>0){
                            builder.append(", ");
                        }
                        builder.append(asp.getValue());
                    }
                }

                cell.setCellValue(builder.toString());
                cell.setCellStyle(styles.get("data"));
                if(builder.length()>45){
                    objects.setHeightInPoints(objects.getHeightInPoints()*(builder.length()/45 + 1));
                }

                if(addition.getAspects().size()>0){
                    aspects = sheet.createRow(delta); delta++;
                    cell = aspects.createCell(1);
                    cell.setCellValue("Аспекты добавлены:");
                    cell.setCellStyle(styles.get("dataNames"));

                    cell = aspects.createCell(2);

                    builder = new StringBuilder();
                    for(Aspect asp: addition.getAspects()){

                        if(!builder.toString().contains(asp.getValue())) {
                            if(builder.length()>0){
                                builder.append(", ");
                            }
                            builder.append(asp.getValue());
                        }
                    }

                    cell.setCellValue(builder.toString());
                    cell.setCellStyle(styles.get("data"));
                    if(builder.length()>45){
                        objects.setHeightInPoints(objects.getHeightInPoints()*(builder.length()/45 + 1));
                    }
                }
                //Examples
                Row examples = sheet.createRow(delta); delta++;
                cell = examples.createCell(1);
                cell.setCellValue("Примеры:");
                cell.setCellStyle(styles.get("dataNames"));

                cell = examples.createCell(2);
                List<Example> exmpls = addition.getPage().getExamples();

                builder = new StringBuilder();

                for(Example exmpl: exmpls){

                    if(builder.length() > 0){
                        builder.append(", ");
                    }
                    builder.append(exmpl.getValue());
                }

                cell.setCellValue(builder.toString());

                cell.setCellStyle(styles.get("data"));
                if(builder.length()>45){
                    examples.setHeightInPoints(examples.getHeightInPoints()*(builder.length()/45 + 1));
                }

                if(addition.getExamples().size()>0){
                    examples = sheet.createRow(delta); delta++;
                    cell = examples.createCell(1);
                    cell.setCellValue("Примеры добавлены:");
                    cell.setCellStyle(styles.get("dataNames"));

                    cell = examples.createCell(2);
                    exmpls = addition.getExamples();
                    builder = new StringBuilder();

                    for(Example exmpl: exmpls){

                        if(builder.length() > 0){
                            builder.append(", ");
                        }
                        builder.append(exmpl.getValue());
                    }

                    cell.setCellValue(builder.toString());

                    cell.setCellStyle(styles.get("data"));
                    if(builder.length()>45){
                        examples.setHeightInPoints(examples.getHeightInPoints()*(builder.length()/45 + 1));
                    }
                }
                sheet.createRow(delta); delta++;
            }

        }
        session.close();
// Write the output to a file


        String file = path+"Report_"+ Translate.toTranslit(author.getFirstName().toUpperCase())+Translate.toTranslit(author.getLastName().toUpperCase())+".xls";
        FileOutputStream out = null;
        try {
            out = new FileOutputStream(file);
            wb.write(out);
            out.close();
        } catch (FileNotFoundException e) {
            e.printStackTrace();  //To change body of catch statement use File | Settings | File Templates.
        } catch (IOException e) {
            e.printStackTrace();  //To change body of catch statement use File | Settings | File Templates.
        }


        return file;
    }


    /**
     * Create a library of cell styles
     */
    private static Map<String, HSSFCellStyle> createStyles(HSSFWorkbook wb){
        Map<String, HSSFCellStyle> styles = new HashMap<String, HSSFCellStyle>();
        HSSFCellStyle style;
        Font titleFont = wb.createFont();
        titleFont.setFontHeightInPoints((short) 14);
        titleFont.setBoldweight(Font.BOLDWEIGHT_BOLD);
        style = wb.createCellStyle();
        style.setAlignment(CellStyle.ALIGN_RIGHT);
        style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
        style.setFont(titleFont);
        style.setWrapText(true);
        styles.put("title", style);


        style = wb.createCellStyle();
        style.setAlignment(CellStyle.ALIGN_LEFT);
        style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
        style.setBorderBottom(HSSFCellStyle.BORDER_THIN);
        style.setFont(titleFont);
        style.setWrapText(true);
        styles.put("subtitle", style);


        style = wb.createCellStyle();
        style.setWrapText(true);
        style.setBorderTop(HSSFCellStyle.BORDER_THIN);
        style.setBottomBorderColor(IndexedColors.BLACK.getIndex());
        styles.put("topBordered", style);



        Font nameFont = wb.createFont();
        nameFont.setFontHeightInPoints((short) 12);
        style = wb.createCellStyle();
        style.setAlignment(CellStyle.ALIGN_CENTER);
        style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
        style.setFont(nameFont);
        styles.put("name", style);


        style = wb.createCellStyle();
        style.setAlignment(CellStyle.ALIGN_LEFT);
        style.setVerticalAlignment(CellStyle.VERTICAL_TOP);
        style.setWrapText(true);
        styles.put("data", style);


        Font dataNameFont = wb.createFont();
        dataNameFont.setFontHeightInPoints((short) 10);
        dataNameFont.setBoldweight(Font.BOLDWEIGHT_BOLD);
        style = wb.createCellStyle();
        style.setAlignment(CellStyle.ALIGN_RIGHT);
        style.setVerticalAlignment(CellStyle.VERTICAL_TOP);
        style.setWrapText(true);
        style.setFont(dataNameFont);
        styles.put("dataNames", style);











        //BOLD FREEZED CELLS
        Font rFont = wb.createFont();
        titleFont.setFontHeightInPoints((short) 12);
        titleFont.setBoldweight(Font.BOLDWEIGHT_BOLD);

        style = wb.createCellStyle();
        style.setAlignment(CellStyle.ALIGN_CENTER);
        style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
        style.setWrapText(true);
        style.setBorderBottom(HSSFCellStyle.BORDER_THICK);
        style.setBorderTop(HSSFCellStyle.BORDER_THICK);
        style.setFont(titleFont);
        styles.put("BOLD", style);

        style = wb.createCellStyle();
        style.setAlignment(CellStyle.ALIGN_CENTER);
        style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
        style.setWrapText(true);
        style.setBorderBottom(HSSFCellStyle.BORDER_THICK);
        style.setBorderTop(HSSFCellStyle.BORDER_THICK);
        styles.put("BOLD_ROW_FORMULA", style);

        style = wb.createCellStyle();
        style.setAlignment(CellStyle.ALIGN_LEFT);
        style.setVerticalAlignment(CellStyle.VERTICAL_BOTTOM);
        style.setWrapText(true);
        style.setBorderBottom(HSSFCellStyle.BORDER_THICK);
        style.setBorderTop(HSSFCellStyle.BORDER_THICK);
        style.setFont(titleFont);
        styles.put("BOLD_LEFT", style);

        style = wb.createCellStyle();
        style.setAlignment(CellStyle.ALIGN_RIGHT);
        style.setVerticalAlignment(CellStyle.VERTICAL_BOTTOM);
        style.setWrapText(true);
        style.setFont(rFont);
        styles.put("BOLD_RIGHT", style);

        style = wb.createCellStyle();
        style.setAlignment(CellStyle.ALIGN_RIGHT);
        style.setVerticalAlignment(CellStyle.VERTICAL_BOTTOM);
        style.setBorderBottom(HSSFCellStyle.BORDER_THICK);
        style.setWrapText(true);
        style.setFont(titleFont);
        styles.put("FREEZE_ROW", style);

        style = wb.createCellStyle();
        style.setAlignment(CellStyle.ALIGN_RIGHT);
        style.setVerticalAlignment(CellStyle.VERTICAL_BOTTOM);
        style.setBorderBottom(HSSFCellStyle.BORDER_THICK);
        style.setWrapText(true);
        styles.put("FREEZE_ROW_FORMULA", style);

        return styles;
    }
}
